<?php

namespace app\admin\controller;

use app\common\controller\AdminBase;
use think\Db;
use think\Log;

class Scholar extends AdminBase
{
    protected function _initialize()
    {
        parent::_initialize();
        if ($this->request->isGet()) {
            $this->assign('institute', model('scholar')->field('institute')->group('institute')->select());
            $this->assign('professionalTitle', model('scholar')->field('professional_title')->group('professional_title')->select());
        }
    }

    /**
     * 学者列表
     */
    public function index()
    {
        $param = $this->request->param();
        $where = [];
        $whereOr = [];
        if (isset($param['name'])) {
            $whereOr['scholar_name'] = ['like', "%" . $param['name'] . "%"];
            $whereOr['email'] = ['like', "%" . $param['name'] . "%"];
            $whereOr['phone'] = ['like', "%" . $param['name'] . "%"];
        }
        if (isset($param['kid'])) {
            $where['kid'] = ['like', "%" . $param['kid'] . "%"];
        }
        if (isset($param['institute'])) {
            $where['institute'] = $param['institute'];
        }
        if (isset($param['professional_title'])) {
            $where['professional_title'] = $param['professional_title'];
        }
        if (isset($param['hometown'])) {
            $where['hometown'] = ['like', "%" . $param['hometown'] . "%"];
        }
        $list = model('scholar')
            ->whereOr(function ($query) use ($whereOr) {
                $query->whereOr($whereOr);
            })
            ->where(function ($query) use ($where) {
                $query->where($where);
            })
            ->paginate(config('page_number'), false, ['query' => $param]);
        return $this->fetch('index', ['list' => $list]);
    }

    /**
     * 下载学者样例
     */
    public function downloadOrUpload() {
        $file = '.' . "/template/1-学者.xlsx";
        if (file_exists($file)) {
            ob_end_clean();
            header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
            header('Content-Description: File Transfer');
            header('Content-Type: application/octet-stream');
            header('Content-Length: ' . filesize($file));
            header('Content-Disposition: attachment; filename=' . basename($file));
            readfile($file);
            $this->result('', 1, '获取地址成功');
        } else {
            throw new \Exception("文件不存在");
        }
    }

    /**
     * 导入学者
     */
    public function importScholar()
    {
        if ($this->request->isPost()) {
            // 获取参数中的excel
            $file = $this->request->file('file');
            // 获取导入数据
            $excel_array = import_excel($file);
            if ($excel_array == null || sizeof($excel_array) == 0) {
                return $this->error('导入失败，请确认类型或者文件是否有效');
            }
            $dbData = [];
            foreach ($excel_array as $k => $v) {
                $dbData[$k]['kid'] = $v['0'];
                $dbData[$k]['employee_number'] = $v['0'];
                $dbData[$k]['scholar_name'] = $v['1'];
                $dbData[$k]['date_of_birth'] = $v['2'];
                $dbData[$k]['sex'] = $v['3'];
                $dbData[$k]['id_number'] = $v['4'];
                $dbData[$k]['first_level_department'] = $v['5'];
                $dbData[$k]['secondary_department'] = $v['6'];
                $dbData[$k]['tertiary_department'] = $v['7'];
                $dbData[$k]['nationality'] = $v['8'];
                $dbData[$k]['hometown'] = $v['9'];
                $dbData[$k]['political_status'] = $v['10'];
                $dbData[$k]['operating_hours'] = $v['11'];
                $dbData[$k]['school_time'] = $v['12'];
                $dbData[$k]['compilation_category'] = $v['13'];
                $dbData[$k]['job_category'] = $v['14'];
                $dbData[$k]['nature_of_position'] = $v['15'];
                $dbData[$k]['job_level_employed'] = $v['16'];
                $dbData[$k]['shoulders'] = $v['17'];
                $dbData[$k]['whether_part_time_teacher'] = $v['18'];
                $dbData[$k]['master_or_phd_supervisor'] = $v['19'];
                $dbData[$k]['personnel_status'] = $v['20'];
                $dbData[$k]['highest_education'] = $v['21'];
                $dbData[$k]['graduated_school'] = $v['22'];
                $dbData[$k]['graduation_time'] = $v['23'];
                $dbData[$k]['major'] = $v['24'];
                $dbData[$k]['highest_degree'] = $v['25'];
                $dbData[$k]['degree_granting_school'] = $v['26'];
                $dbData[$k]['degree_time'] = $v['27'];
                $dbData[$k]['subject'] = $v['28'];
                $dbData[$k]['first_level_discipline'] = $v['29'];
                $dbData[$k]['affiliation_second_department'] = $v['30'];
                $dbData[$k]['party_and_government_positions'] = $v['31'];
                $dbData[$k]['job_level'] = $v['32'];
                $dbData[$k]['working_time'] = $v['33'];
                $dbData[$k]['administrative_rank'] = $v['34'];
                if ($v['35'] == null) {
                    $v['35'] = '1970-01-01 08:00:00';
                }
                $dbData[$k]['rank_assessment_time'] = $v['35'];
                $dbData[$k]['professional_title'] = $v['36'];
                $dbData[$k]['title_level'] = $v['37'];
                $dbData[$k]['title_evaluation_time'] = $v['38'];
                $dbData[$k]['technical_job_name'] = $v['39'];
                $dbData[$k]['skilled_worker_level'] = $v['40'];
                if ($v['41'] == null) {
                    $v['41'] = '1970-01-01 08:00:00';
                }
                $dbData[$k]['technician_appointment_time'] = $v['41'];
                $dbData[$k]['whether_to_study_abroad'] = $v['42'];
                $dbData[$k]['study_country'] = $v['43'];
                $dbData[$k]['language_one'] = $v['44'];
                $dbData[$k]['language_two'] = $v['45'];
                $dbData[$k]['phone'] = $v['46'];
                $dbData[$k]['family_address'] = $v['47'];
                $dbData[$k]['office_phone'] = $v['48'];
                $dbData[$k]['email'] = $v['49'];
                $dbData[$k]['create_time'] = date('Y-m-d H:i:s');
                $dbData[$k]['update_time'] = date('Y-m-d H:i:s');
            }
            //批量插入数据
            $success = db::name('scholar')->insertAll($dbData, true);

            $this->success('导入成功', url('admin/scholar/index'));
        }
        return $this->fetch('importScholar', ['scholar' => model('scholar')->order('id asc')->select()]);
    }

    public function add()
    {
        if ($this->request->isPost()) {
            $param = $this->request->param();
            $where = [];
            if (isset($param['kid'])) {
                $where['kid'] = $param['kid'];
            }
            $scholar = model('scholar')
                ->where($where)
                ->find();
            if (!empty($scholar)) {
                $this->error("该学者编号已存在");
            }

            $scholar = array();
            $scholar['scholar_name'] = $param['scholar_name'];
            $scholar['kid'] = $param['kid'];
            $scholar['institute'] = $param['institute'];
            $scholar['entrant_time'] = $param['entrant_time'];
            $scholar['sex'] = $param['sex'];
            $scholar['email'] = $param['email'];
            $scholar['phone'] = $param['phone'];
            $scholar['professional_title'] = $param['professional_title'];
            $scholar['create_time'] = date("Y-m-d H:i:s");

            if ($this->insert('scholar', $scholar, false) === true) {
                insert_admin_log('添加了学者');

                if (isset($param['year_from'])) {
                    // 学者履历
                    $yearFroms = [];
                    if (strpos($param['year_from'], ',') !== false) {
                        $yearFroms = explode(',', $param['year_from']);
                    } else {
                        $yearFroms[0] = $param['year_from'];
                    }
                    $yearTos = [];
                    if (strpos($param['year_to'], ',') !== false) {
                        $yearTos = explode(',', $param['year_to']);
                    } else {
                        $yearTos[0] = $param['year_to'];
                    }
                    $agencies = [];
                    if (strpos($param['agency'], ',') !== false) {
                        $agencies = explode(',', $param['agency']);
                    } else {
                        $agencies[0] = $param['agency'];
                    }
                    $educations = [];
                    if (strpos($param['education'], ',') !== false) {
                        $educations = explode(',', $param['education']);
                    } else {
                        $educations[0] = $param['education'];
                    }
                    for ($i = 0; $i < sizeof($yearFroms); $i++) {
                        $scholarResume = [];
                        $scholarResume['kid'] = $scholar['kid'];
                        $scholarResume['year_from'] = $yearFroms[$i];
                        $scholarResume['year_to'] = $yearTos[$i];
                        $scholarResume['agency'] = $agencies[$i];
                        $scholarResume['education'] = $educations[$i];
                        $scholarResume['create_time'] = date("Y-m-d H:i:s");
                        $scholarResume['update_time'] = date("Y-m-d H:i:s");
                        db('scholarResume')->insert($scholarResume);
                    }
                }
                if (isset($param['year'])) {
                    // 奖惩信息
                    $years = [];
                    if (strpos($param['year'], ',') !== false) {
                        $years = explode(',', $param['year']);
                    } else {
                        $years[0] = $param['year'];
                    }
                    $resumes = [];
                    if (strpos($param['rewardAndPunish'], ',') !== false) {
                        $resumes = explode(',', $param['rewardAndPunish']);
                    } else {
                        $resumes[0] = $param['rewardAndPunish'];
                    }
                    $dbData = array();
                    for ($i = 0; $i < sizeof($years); $i++) {
                        $dbData[$i]['year'] = $years[$i];
                        $dbData[$i]['resume'] = $resumes[$i];
                    }
                    $dbJson = json_encode($dbData);
                    $userOrgCreator = [];
                    $userOrgCreator['scholar_id'] = $scholar['kid'];
                    $userOrgCreator['resume'] = $dbJson;
                    db('userOrgCreator')->insert($userOrgCreator);
                }

                $this->success('添加成功', url('admin/scholar/index'));
            } else {
                $this->error($this->errorMsg);
            }
        }
        return $this->fetch('add', ['scholar' => model('scholar')->order('id asc')->select()]);
    }

    /**
     * 学者编辑
     */
    public function edit()
    {
        if ($this->request->isPost()) {
            $param = $this->request->param();
            $param['update_time'] = date("Y-m-d H:i:s");
            if ($this->update('scholar', $param, false, input('_verify', true)) === true) {
                insert_admin_log('修改了学者');
                $this->success('修改成功', url('admin/scholar/index'));
            } else {
                $this->error($this->errorMsg);
            }
        }
        $scholar = model('scholar')->where('id', input('id'))->find();
        return $this->fetch('save', [
            'data'     => $scholar,
            'scholar' => model('scholar')->order('id asc')->select(),
        ]);
    }

    public function checkPass()
    {
        if ($this->request->isPost()) {
            $param = $this->request->param();
            if (is_array($param['id'])) {
                foreach ($param['id'] as $v) {
                    db('scholar')->where([
                        'id' => $v
                    ])->update([
                        'is_check' => 1
                    ]);
                }
            } else {
                db('scholar')->where([
                    'id' => $param['id']
                ])->update([
                    'is_check' => 1
                ]);
            }
            $this->success('审核成功', url('admin/scholar/index'));
        }
        $this->error('参数错误');
    }

    public function checkNotPass()
    {
        if ($this->request->isPost()) {
            $param = $this->request->param();
            if (is_array($param['id'])) {
                foreach ($param['id'] as $v) {
                    db('scholar')->where([
                        'id' => $v
                    ])->update([
                        'is_check' => 2
                    ]);
                }
            } else {
                db('scholar')->where([
                    'id' => $param['id']
                ])->update([
                    'is_check' => 2
                ]);
            }
            $this->success('审核成功', url('admin/scholar/index'));
        }
        $this->error('参数错误');
    }

    /**
     * 学者履历
     */
    public function resumeDetail()
    {
        $param = $this->request->param();
        $where = [];
        if (isset($param['kid'])) {
            $where['kid'] = $param['kid'];
        }
        $list = model('scholarResume')
            ->where($where)
            ->order('year_to desc')
            ->paginate(config('page_number'), false, ['query' => $param]);
        return $this->fetch('resumeDetail', ['id' => $param['id'], 'kid' => $param['kid'], 'list' => $list]);
    }

    /**
     * 学者履历添加
     */
    public function resumeAdd()
    {
        $param = $this->request->param();
        if ($this->request->isPost()) {
            $param['create_time'] = date("Y-m-d H:i:s");
            $param['update_time'] = date("Y-m-d H:i:s");
            if ($this->insert('scholarResume', $param, false) === true) {
                insert_admin_log('添加了学者履历');
                $this->success('添加成功', url('admin/scholar/resumeDetail'));
            } else {
                $this->error($this->errorMsg);
            }
        }
        return $this->fetch('resumeSave', ['kid' => $param['kid'], 'scholarResume' => model('scholarResume')->order('id asc')->select()]);
    }

    /**
     * 学者履历编辑
     */
    public function resumeEdit()
    {
        if ($this->request->isPost()) {
            $param = $this->request->param();
            $param['update_time'] = date("Y-m-d H:i:s");
            if ($this->update('scholarResume', $param, false, input('_verify', true)) === true) {
                insert_admin_log('修改了学者履历');
                $this->success('修改成功', url('admin/scholar/resumeDetail'));
            } else {
                $this->error($this->errorMsg);
            }
        }
        return $this->fetch('resumeSave', [
            'data'     => model('scholarResume')->where('id', input('id'))->find(),
            'scholarResume' => model('scholarResume')->order('id asc')->select(),
        ]);
    }

    /**
     * 学者履历删除
     */
    public function resumeDel()
    {
        if ($this->request->isPost()) {
            if ($this->delete('scholarResume', $this->request->param()) === true) {
                insert_admin_log('删除了学者履历');
                $this->success('删除成功');
            } else {
                $this->error($this->errorMsg);
            }
        }
    }

    public function toRewardAndPunish() {
        $param = $this->request->param();
        $scholar = model('scholar')->where('kid', '=', $param['kid'])->find();
        if (empty($scholar)) {
            $this->error('该学者信息不存在');
        }
        return $this->fetch('rewardAndPunish', ['id' => $param['id'], 'kid' => $param['kid'], 'name' => $scholar['scholar_name']]);
    }

    /**
     * 学者奖惩信息
     */
    public function rewardAndPunish()
    {
        $param = $this->request->param();
        $where = [];
        if (isset($param['kid'])) {
            $where['scholar_id'] = $param['kid'];
        }
        $list = model('userOrgCreator')
            ->where($where)
            ->select();
        $num = 0;
        $rewardAndPunishes = array();
        foreach ($list as $v) {
            if (empty($v['resume'])) {
                continue;
            }
            $jsonData = json_decode($v['resume'], true);
            for($i=0; $i<count($jsonData); $i++){
                if (empty($jsonData[$i]['year']) && empty($jsonData[$i]['resume'])) {
                    continue;
                }
                $oneRes = [];
                $oneRes["year"] = $jsonData[$i]['year'];
                $oneRes["resume"] = $jsonData[$i]['resume'];
                $rewardAndPunishes[$num] = $oneRes;
                $num++;
            }
        }
        $this->result($rewardAndPunishes, 1);
    }

    public function rewardAndPunishSave() {
        $param = $this->request->param();
        $scholar = model('scholar')->where('kid', '=', $param['kid'])->find();
        if (empty($scholar)) {
            $this->error('该学者信息不存在');
        }
        $years = [];
        if (strpos($param['year'], ',') !== false) {
            $years = explode(',', $param['year']);
        } else {
            $years[0] = $param['year'];
        }
        $resumes = [];
        if (strpos($param['resume'], ',') !== false) {
            $resumes = explode(',', $param['resume']);
        } else {
            $resumes[0] = $param['resume'];
        }
        $dbData = array();
        for ($i = 0; $i < sizeof($years); $i++) {
            $dbData[$i]['year'] = $years[$i];
            $dbData[$i]['resume'] = $resumes[$i];
        }
        $dbJson = json_encode($dbData);

        $where = [];
        if (isset($param['kid'])) {
            $where['scholar_id'] = $param['kid'];
        }
        $userOrgCreator = model('userOrgCreator')
            ->where($where)
            ->find();
        if (empty($userOrgCreator)) {
            $userOrgCreator['scholar_id'] = $param['kid'];
            $userOrgCreator['resume'] = $dbJson;
            db('userOrgCreator')->insert($userOrgCreator);
        } else {
            db('userOrgCreator')->where([
                'scholar_id' => $param['kid']
            ])->update([
                'resume' => $dbJson
            ]);
        }
        $this->success('保存成功');
    }

    /**
     * 学者成果
     */
    public function resultIndex()
    {
        $param = $this->request->param();
        $where = [];
        $whereOr = [];
        if (isset($param['name'])) {
            $whereOr['s.scholar_name'] = ['like', "%" . $param['name'] . "%"];
            $whereOr['s.email'] = ['like', "%" . $param['name'] . "%"];
            $whereOr['s.phone'] = ['like', "%" . $param['name'] . "%"];
        }
        if (isset($param['institute'])) {
            $where['s.institute'] = $param['institute'];
        }
        if (isset($param['professional_title'])) {
            $where['s.professional_title'] = $param['professional_title'];
        }

        $list = db('scholar')->alias('s')
            ->join('user u', 's.kid = u.user_no', 'left')
            ->field('s.*, u.id as userId')
            ->whereOr(function ($query) use ($whereOr) {
                $query->whereOr($whereOr);
            })
            ->where(function ($query) use ($where) {
                $query->where($where);
            })
            ->paginate(config('page_number'), false, ['query' => $param]);
        if (!empty($list)) {
            $userIds = '';
            foreach ($list as $k => $v) {
                $userIds .= ',' . $v['userId'];
            }
            $typeResources = db('user_resource')
                ->group('user_id, resource_code')
                ->field('user_id, resource_code as resourceCode, count(*) as count')
                ->whereIn('user_id', $userIds)
                ->select();
            if (!empty($typeResources)) {
                $items = $list -> items();
                foreach ($items as $k => $v) {
                    $other = 0;
                    foreach ($typeResources as $userTypeResource) {
                        if ($userTypeResource['user_id'] != $v['userId']) {
                            continue;
                        }
                        // 中文论文
                        if ($userTypeResource['resourceCode'] == 'J') {
                            $v['paper_num'] = $userTypeResource['count'];
                        }
                        // SCI
                        else if ($userTypeResource['resourceCode'] == 'I') {
                            $v['sci_num'] = $userTypeResource['count'];
                        }
                        // EI
                        else if ($userTypeResource['resourceCode'] == 'E') {
                            $v['ei_num'] = $userTypeResource['count'];
                        }
                        // 专利
                        else if ($userTypeResource['resourceCode'] == 'P') {
                            $v['patent_num'] = $userTypeResource['count'];
                        }
                        // 其他
                        else {
                            $other += $userTypeResource['count'];
                        }
                    }
                    $v['other_num'] = $other;
                    $list[$k] = $v;
                }
                return $this->fetch('result', ['list' => $list]);
            }
        }
        return $this->fetch('result', ['list' => $list]);
    }

    /**
     * 学者成果详情
     */
    public function resultDetail()
    {
        $param = $this->request->param();
        $where = [];
        if (isset($param['scholarId'])) {
            $where['s.id'] = $param['scholarId'];
        }
        if (isset($param['resourceCode'])) {
            if ($param['resourceCode'] == 'other') {
                $list = db('scholar')->alias('s')
                    ->join('user u', 'u.user_no = s.kid', 'inner')
                    ->join('user_resource ur', 'ur.user_id = u.id', 'inner')
                    ->join('local_resource_collection rc', 'ur.resource_id = rc.id', 'inner')
                    ->field('ur.id as id, rc.title, rc.year, rc.journal, rc.creatorAll, rc.creatorStandard, rc.instituteAll, rc.instituteStandard, rc.keyword')
                    ->where($where)
                    ->whereNotIn('rc.resourceCode', 'J,I,E,P')
                    ->order('rc.create_time desc')
                    ->paginate(config('page_number'), false, ['query' => $param]);
                return $this->fetch('resultDetail', ['scholarId' => $param['scholarId'], 'resourceCode' => $param['resourceCode'], 'list' => $list]);
            } else {
                $where['rc.resourceCode'] = $param['resourceCode'];
            }
        } else {
            $param['resourceCode'] = 'J';
            $where['rc.resourceCode'] = $param['resourceCode'];
        }
        $list = db('scholar')->alias('s')
            ->join('user u', 'u.user_no = s.kid', 'inner')
            ->join('user_resource ur', 'ur.user_id = u.id', 'inner')
            ->join('local_resource_collection rc', 'ur.resource_id = rc.id', 'inner')
            ->field('ur.id as id, rc.title, rc.year, rc.journal, rc.creatorAll, rc.creatorStandard, rc.instituteAll, rc.instituteStandard, rc.keyword')
            ->where($where)
            ->order('rc.create_time desc')
            ->paginate(config('page_number'), false, ['query' => $param]);
        return $this->fetch('resultDetail', ['scholarId' => $param['scholarId'], 'resourceCode' => $param['resourceCode'], 'list' => $list]);
    }

    /**
     * 学者成果删除
     */
    public function resultDel()
    {
        if ($this->request->isPost()) {
            if ($this->delete('userResource', $this->request->param()) === true) {
                insert_admin_log('删除了认领成果');
                $this->success('删除成功');
            } else {
                $this->error($this->errorMsg);
            }
        }
    }

    public function portrayal()
    {
        return $this->fetch('portrayal');
    }

    /**
     * 性别分布
     */
    public function sexChart()
    {
        $sexes = model('scholar')->field('sex, count(sex) as count')
            ->group('sex')->order('sex')->select()->toArray();
        $obj['sexes'] = $sexes;
        return $this->result($obj, 1);
    }

    /**
     * 年龄分布
     */
    public function ageChart()
    {
        $sql = "select ageScope,count(ageScope) count from ( "
            ."SELECT CASE "
            ."when age<35 then '<35' "
            ."when age>=35 and age<45 then '35~45' "
            ."when age>=45 and age<55 then '45~55' "
            ."when age>=55 and age<65 then '55~65' "
            ."when age>=65 then '>65' "
            ."END "
            ."as ageScope from scholar "
            .")a GROUP BY ageScope";
        $ages = model('scholar')->query($sql);
        $obj['ages'] = $ages;
        return $this->result($obj, 1);
    }

    /**
     * 籍贯分布
     */
    public function hometownChart()
    {
        $hometowns = model('scholar')->field('hometown as name, count(hometown) as value')
            ->group('hometown')->order('hometown')->select()->toArray();
        $obj['hometowns'] = $hometowns;
        return $this->result($obj, 1);
    }

    /**
     * 学位分布、学科门类分布、职称分布
     */
    public function subjectChart()
    {
        $academicDegrees = model('scholar')->where('academic_degree', '<>', '')
            ->field('academic_degree as name, count(academic_degree) as value')
            ->group('academic_degree')->order('academic_degree')->select()->toArray();
        $subjects = model('scholar')->where('subject', '<>', '')
            ->field('subject as name, count(subject) as value')
            ->group('subject')->order('value', 'desc')->limit(4)->select()->toArray();
        $professionalTitles = model('scholar')->where('professional_title', '<>', '')
            ->field('professional_title as name, count(professional_title) as value')
            ->group('professional_title')->order('value', 'desc')->select()->toArray();
        $obj['academicDegrees'] = $academicDegrees;
        $obj['subjects'] = $subjects;
        $obj['professionalTitles'] = $professionalTitles;
        return $this->result($obj, 1);
    }

    /**
     * 年代期刊分布
     */
    public function yearChart()
    {
        $cooperateList = model('collection')->field('year,COUNT(resourceCode) count,resourceCode')
            ->group('year,resourceCode')->order('year,resourceCode')->select()->toArray();
        $obj['cooperateList'] = $cooperateList;
        return $this->result($obj, 1);
    }

}
